/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DBLayer;

import ModelLayer.Invoice;
import java.sql.Statement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author DM72-GRP2
 */
public class DBInvoice extends DBExtentions implements IFDBInvoice {

    private Connection con;
    private ArrayList<String>queryList = new ArrayList<String>();


    public DBInvoice()
    {
        con = DbConnection.getInstance().getDBcon();
    }

    public ArrayList<Invoice> getAllInvoice()
    {
        return miscWhere("");
    }

    public Invoice searchInvoiceNo(int invNo)
    {
        String wClause = " invoiceNo = '" + invNo + "'";
        return singleWhere(wClause);
    }

    public int insertInvoice(Invoice inv)
    {
        queryList.clear();
        int nextInvoiceID = GetMax.getMaxId("Select max(invoiceNo) from Invoice");
        nextInvoiceID = nextInvoiceID + 1;
        System.out.println("next InvoiceID = " + nextInvoiceID);

        int rc = -1;
        //Transaktion 1 Begins
        String query = "INSERT INTO Invoice(invoiceID, invoiceNo, paymentDate, amount, statusID)  VALUES('"
                + nextInvoiceID + "','"
                + nextInvoiceID + "','"
                + inv.getPaymentDate() + "','"
                + inv.getAmount() + "',"
                + inv.getStatusID() + "')";

        System.out.println("insert : " + query);
        try
        {
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            rc = stmt.executeUpdate(query);
            stmt.close();
        }
        catch (Exception ex)
        {
            System.out.println("Insert exception in Invoice db: " + ex);
        }
        return (rc);
    }

    public int updateInvoice(Invoice inv)
    {
        queryList.clear();
        Invoice invObj = inv;
        int rc = -1;

        String query = "UPDATE Invcoice SET "
                + "paymentDate ='" + invObj.getPaymentDate() + "','"
                + "amount ='" + invObj.getAmount() + "',"
                + "statusID ='" + invObj.getStatusID() + "'"
                + "WHERE invoiceID = '" + invObj.getInvoiceNo() + "'";

        System.out.println("Update query:" + query);
        queryList.add(query);
        rc = rollBack(queryList);
        return rc;
    }

    public int deleteInvoice(int invID)
    {
        queryList.clear();
        int rc = -1;
        //Transaktion 1 Begin
        String query = "DELETE FROM Invoice WHERE invoiceID = '"
                + invID + "'";
        System.out.println("Delete Ref " + query);
        //Transaktion 1 Ends
        rc = rollBack(queryList);
        return rc;
    }

    private ArrayList<Invoice> miscWhere(String wClause)
    {
        ResultSet results;
        ArrayList<Invoice> list = new ArrayList<Invoice>();
        String query = buildQuery(wClause);
        try
        { // read from database
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);
            while (results.next())
            {
                Invoice invObj = new Invoice();
                invObj = buildInvoice(results);
                list.add(invObj);
            }//end while
            stmt.close();
        }//slut try
        catch (Exception e)
        {
            System.out.println("Query exception - select: " + e);
            e.printStackTrace();
        }
        return list;
    }
    
    //Singelwhere is used when we only select one invoice
    private Invoice singleWhere(String wClause)
    {
        ResultSet results;
        Invoice invObj = new Invoice();
        String query = buildQuery(wClause);
        System.out.println(query);
        try
        { // read the invoice from the database
            Statement stmt = con.createStatement();
            stmt.setQueryTimeout(5);
            results = stmt.executeQuery(query);

            if (results.next())
            {
                invObj = buildInvoice(results);
            }
        }//end try
        catch (Exception e)
        {
            System.out.println("Query exception: " + e);
        }
        return invObj;
    }

    //method to build the query
    private String buildQuery(String wClause)
    {
        String query = "SELECT * FROM Invoice P, Status S";
        if (wClause.length() > 0)
           query=query+" WHERE "+ wClause;
        return query;
    }
    //method to build an invoice object

    private Invoice buildInvoice(ResultSet results)
    {
        Invoice invObj = new Invoice();
        try
        {
            invObj.setInvoiceNo(results.getInt("invoiceNo"));
            invObj.setPaymentDate(results.getString("paymentDate"));
            invObj.setAmount(results.getDouble("amount"));
            invObj.setStatus(results.getString("stastusID"));
        }
        catch (Exception e)
        {
            System.out.println("error in building the Invoice object");
        }
        return invObj;
    }

}
